﻿Imports System.Data.SQLite
Imports System.IO


Public Class BD
    Private Conexion As SQLiteConnection
    Public Function Conectar() As Boolean
        Conexion = New SQLiteConnection("Data Source=bd.sqlite;Version=3")

        Conexion.Open()


        Return True
    End Function

    Public Sub Cerrar()
        conexion.Close()
    End Sub

    Public Function InsertarCliente(ByRef MiCliente As Cliente) As Boolean

        Dim param1 As SQLiteParameter = New SQLiteParameter()
        param1.Value = MiCliente.Dni()

        Dim param2 As SQLiteParameter = New SQLiteParameter()
        param2.Value = MiCliente.Nombre()

        Dim param3 As SQLiteParameter = New SQLiteParameter()
        param3.Value = MiCliente.Telefono()

        Dim param4 As SQLiteParameter = New SQLiteParameter()
        param4.Value = MiCliente.Email()

        Dim param5 As SQLiteParameter = New SQLiteParameter()
        param5.Value = MiCliente.Whatssapp()

        Dim param6 As SQLiteParameter = New SQLiteParameter()
        param6.Value = MiCliente.Facebook()
        Dim param7 As SQLiteParameter = New SQLiteParameter()
        param7.Value = MiCliente.Twitter()

        Dim sql As String = "insert into cliente values(?,?,?,?,?,?,?);"

        Dim cmd As New SQLiteCommand(sql, Conexion)

        cmd.Parameters.Add(param1)
        cmd.Parameters.Add(param2)
        cmd.Parameters.Add(param3)
        cmd.Parameters.Add(param4)
        cmd.Parameters.Add(param5)
        cmd.Parameters.Add(param6)
        cmd.Parameters.Add(param7)

        Dim afectado As Integer = cmd.ExecuteNonQuery()

        If (afectado > 0) Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Function CargarClientes() As ArrayList
        Dim ArrayClientes As New ArrayList
        Dim Reader As SQLiteDataReader
        Dim Sql As String = "select * from cliente;"
        Dim Cmd As New SQLiteCommand(Sql, Conexion)
        Dim MiCliente As Cliente
        Dim W As Boolean


        Reader = Cmd.ExecuteReader()

        If (Reader.HasRows) Then

            Do While Reader.Read()
                MiCliente = New Cliente()
                MiCliente.Dni = Reader("dni")
                MiCliente.Nombre = Reader("nombre")
                MiCliente.Telefono = Reader("telefono")
                MiCliente.Email = Reader("email")
                W = Boolean.Parse(Reader("whatssapp"))
                If (W = 0) Then
                    MiCliente.Whatssapp = False
                Else
                    MiCliente.Whatssapp = True
                End If

                MiCliente.Facebook = Reader("facebook")
                MiCliente.Twitter = Reader("twitter")
                ArrayClientes.Add(MiCliente)

            Loop
        End If

        Reader.Close()


        Return ArrayClientes
    End Function

    Public Function CargarCliente(Dni As String, ByRef MiCliente As Cliente) As Boolean
        MiCliente = New Cliente()
        Dim Reader As SQLiteDataReader
        Dim Sql As String = "select * from cliente where dni='" + Dni + "';"
        Dim Cmd As New SQLiteCommand(Sql, Conexion)
        Dim W As Boolean

        Reader = Cmd.ExecuteReader()
        If (Reader.HasRows) Then
            Reader.Read()
            MiCliente = New Cliente()
            MiCliente.Dni = Reader("dni")
            MiCliente.Nombre = Reader("nombre")
            MiCliente.Telefono = Reader("telefono")
            MiCliente.Email = Reader("email")
            W = Boolean.Parse(Reader("whatssapp"))
            If (W = 0) Then
                MiCliente.Whatssapp = False
            Else
                MiCliente.Whatssapp = True
            End If

            MiCliente.Facebook = Reader("facebook")
            MiCliente.Twitter = Reader("twitter")
        Else
            Return False
        End If

        Return True
    End Function

    Public Function RecuperarReservas(Fecha As DateTime) As ArrayList
        ' Tendremos los arrays de reservas de un dia concreto
        Dim ArrayReservas As New ArrayList()
        Dim Reader As SQLiteDataReader
        Dim DayString As String = Fecha.Day.ToString()
        If (Fecha.Day() < 10) Then
            DayString = "0" + DayString
        End If
        Dim MonthString As String = Fecha.Month.ToString()
        If (Fecha.Month < 10) Then
            MonthString = "0" + MonthString
        End If


        Dim Sql As String = "select * from reserva where date(fecha_inicio)=date('" + Fecha.Year.ToString() + "-" + MonthString + "-" + DayString + "');"
        Dim Cmd As New SQLiteCommand(Sql, Conexion)
        Dim MiCliente As Cliente
        Dim MiPista As Pista
        Dim MiReserva As Reserva
        Dim MiString As String

        Reader = Cmd.ExecuteReader()

        If (Reader.HasRows) Then
            Do While (Reader.Read())
                MiString = Reader("dni").ToString()
                ArrayReservas.Add(MiString)
            Loop
        Else
            MiString = Sql
            ArrayReservas.Add(MiString)
        End If

        Return ArrayReservas
    End Function

    Public Function ModificarCliente(MiCliente As Cliente) As Boolean
        Dim Sql As String = "update cliente SET nombre=?, telefono=?, email=?, whatssapp=?, facebook=?, twitter=? where dni=?; "

        Dim ParamNombre As SQLiteParameter = New SQLiteParameter()
        ParamNombre.Value = MiCliente.Nombre()

        Dim ParamTelefono As SQLiteParameter = New SQLiteParameter()
        ParamTelefono.Value = MiCliente.Telefono()

        Dim ParamEmail As SQLiteParameter = New SQLiteParameter()
        ParamEmail.Value = MiCliente.Email()

        Dim ParamWhatssapp As SQLiteParameter = New SQLiteParameter()
        ParamWhatssapp.Value = MiCliente.Whatssapp()

        Dim ParamFacebook As SQLiteParameter = New SQLiteParameter()
        ParamFacebook.Value = MiCliente.Facebook()

        Dim ParamTwitter As SQLiteParameter = New SQLiteParameter()
        ParamTwitter.Value = MiCliente.Twitter()

        Dim ParamDNI As SQLiteParameter = New SQLiteParameter()
        ParamDNI.Value = MiCliente.Dni()

        Dim Cmd As New SQLiteCommand(Sql, Conexion)
        Cmd.Parameters.Add(ParamNombre)
        Cmd.Parameters.Add(ParamTelefono)
        Cmd.Parameters.Add(ParamEmail)
        Cmd.Parameters.Add(ParamWhatssapp)
        Cmd.Parameters.Add(ParamFacebook)
        Cmd.Parameters.Add(ParamTwitter)
        Cmd.Parameters.Add(ParamDNI)

        Dim afectado As Integer = Cmd.ExecuteNonQuery()

        If (afectado > 0) Then
            Return True
        Else
            Return False
        End If






    End Function

    Public Function CargarReservasCliente(MiCliente As Cliente) As SQLiteDataAdapter
        Dim Sql As String = "select * from reserva;"


        Dim Adapter As New SQLiteDataAdapter(Sql, Conexion)

        Return Adapter
    End Function

    Public Sub Backup()
        'Recuperar todos lso clientes

        Dim Archivo As String = "backup.sql"
        Dim Fich As New System.IO.StreamWriter(Archivo)

        Dim Sql As String = "select * from cliente;"
        Dim Insert As String = "Delete from cliente; " + vbNewLine +"Insert into cliente values('"
        Dim Cmd As SQLiteCommand = New SQLiteCommand(Sql, Conexion)
        Dim Reader As SQLiteDataReader
        Reader = Cmd.ExecuteReader()


        Do While (Reader.Read())
            Insert += Reader("dni") + "','" + Reader("nombre") + "','" + Reader("telefono") + "','" + Reader("email") + "','" + Reader("whatssapp").ToString() + "','" + Reader("facebook") + "','" + Reader("twitter") + "');"
            Fich.WriteLine(Insert)
            Insert = "Insert into cliente values('"
        Loop

        Sql = "select * from pista;"

        Cmd = New SQLiteCommand(Sql, Conexion)
        Reader = Cmd.ExecuteReader()
        Insert = "Delete from pista;" + vbNewLine + "Insert into pista values("


        Do While (Reader.Read())
            Insert += Reader("numero").ToString() + ",'" + Reader("tipo") + "');"
            Fich.WriteLine(Insert)
            Insert = "Insert into pista values("
        Loop

        Sql = "select * from reserva;"

        Cmd = New SQLiteCommand(Sql, Conexion)
        Reader = Cmd.ExecuteReader()
        Insert = "Delete from reserva;" + vbNewLine + "Insert into reserva values('"

        Do While (Reader.Read())
            Insert += Reader("dni") + "'," + Reader("numero").ToString + ",'" + Reader("tipo") + "'," + Reader("fecha_inicio") + "','" + Reader("fecha_fin") + "');"
            Fich.WriteLine(Insert)
            Insert = "Insert into reserva values("
        Loop

        Fich.Close()

        MessageBox.Show("Base de datos guardada en backup.sql")
    End Sub

    Public Sub Restore()
        Dim NombreFichero As String = "backup.sql"
        Dim Fichero As StreamReader = New StreamReader(NombreFichero)

        Dim Completo As String = Fichero.ReadToEnd()

        Dim Cmd As SQLiteCommand = New SQLiteCommand(Completo, Conexion)

        Cmd.ExecuteNonQuery()

        Fichero.Close()


    End Sub


End Class
